<?php
/**
 * Inserisci qui l'import del model da utilizzare
 */
require_once PATH_BASE.'model/utente.php';


class utentewebfactory {
	
	var $conn;
	
	function __construct()
	{
		$this->conn = template::getInstance()->getConnection();
	}
	
	
	public  function  bindUtenteWeb($username, $password)
	{
		$query ="SELECT COUNT(1)AS num FROM user WHERE upper(username)=upper('".$username."') AND password=MD5('".$password."') AND stato='".USER_ACTIVE."'";
		
		$result = $this->conn->executeQuery($query);
		
		//Trasforma il resultset in un array
		//Questo perche' sei sicuro di avere almeno un risultato
		$row = mysql_fetch_array($result);
		
		$res = intval(($row['num']));
		
		if($res == 0)
		{
			throw new Exception(PREFIX_ERR.ERR_ACCESS_DENIED);
		}
		
		return true;
		
	}
	
	public function getUser($username)
	{
		$querySelectUser = "SELECT ad.username,ad.stato,ad.nome,ad.cognome,ad.profilo FROM user ad WHERE upper(username)=upper('$username')";
		$result = $this->conn->executeQuery($querySelectUser);
	
		$currRow = mysql_fetch_array($result);
	
		$utenteCurr = new Utente($currRow['username'], $currRow['nome'], $currRow['cognome'], $currRow['profilo']);
	
		return $utenteCurr;
	}
	
	
	public function verificaPwdUtente($username,$password)
	{
		$querySelectUser = "SELECT ut.username,ut.passwd FROM autore ut WHERE username='$username'";
		$result = $this->conn->executeQuery($querySelectUser);
	
		$MD5pwd = md5($password);
	
		$currRow = mysql_fetch_array($result);
	
		if( $currRow['passwd']==$MD5pwd){
			return true;
		}else{
			return false;
		}
	}
	
	public function aggiornaPwdUtente($username,$new_password)
	{
		try{
			$MD5pwd = md5($new_password);
	
			$queryUpdate = "UPDATE autore SET passwd='$MD5pwd' WHERE username='$username'";
			$result = $this->conn->executeQuery($queryUpdate);
	
		}catch (Exception $e) {
			throw new Exception(ERR_INS_NEW_PWD_USER);
		}
		return true;
	}
	

	public function updateProfiloUtente($usernameUtente ,$nome,$cognome,$email)
	{
		try {
			$queryUpdate  = "UPDATE autore SET nome='$nome',cognome='$cognome',email='$email'
			WHERE username='$usernameUtente'";
			$this->conn->executeUpdate($queryUpdate);
		} catch (Exception $e) {
			throw new Exception(ERR_UPDATE_OPERATOR);
		}
		return true;
	}
	
	public function createUtente($username,$password,$email,$stato,$profiloUt,$nome,$cognome)
	{
	
		if($this->existsUsername($username))
		{
			throw new Exception(PREFIX_ERR.ERR_USERNAME_ALREADY_EXISTS);
		}
		else
		{
	
			$MD5pwd = md5($password);
	
			$queryInsert  = "INSERT INTO autore (username,nome,cognome,passwd, stato,email,profilo) values('$username','$nome','$cognome','$MD5pwd','$stato','$email','$profiloUt')";
	
			$this->conn->executeUpdate($queryInsert);
	
			return true;
	
		}
	
	}
	
	public function existsUsername($username)
	{
		$queryCheckUsername = "SELECT COUNT(*) AS num FROM autore WHERE upper(username)=upper('$username')";
		$result = $this->conn->executeQuery($queryCheckUsername);
	
		//Trasforma il resultset in un array
		//Questo perchè sei sicuro di avere almeno un risultato
		$row = mysql_fetch_array($result);
	
		$res = intval(($row['num']));
	
		if($res == 0)
		{
			return false;
		}
	
		return true;
	}
	
	//Sopra le funzioni utilizzate ----------------------
	
	
	
	
	
	
	public  function  bindUtenteWebCookie($username, $passwordMD5)
	{
		$query ="SELECT COUNT(1)AS num FROM utenti WHERE upper(username)=upper('".$username."') AND pwd='".$passwordMD5."' AND stato='".STATO_ACTIVE."'";
		
		$result = $this->conn->executeQuery($query);
		
		//Trasforma il resultset in un array
		//Questo perche' sei sicuro di avere almeno un risultato
		$row = mysql_fetch_array($result);
		
		$res = intval(($row['num']));
		
		if($res == 0)
		{
			return false;
		}
		
		return true;
		
	}
	

	

	
	
	public function listaPermessiUtenteWeb($utente)
	{
		$lista;
		$querySelect = "SELECT * FROM permission WHERE fk_utenteweb='$utente'";

        $result = $this->conn->executeQuery($querySelect);
        $ind =0;
        $lista  = array();
        while ($currRow = mysql_fetch_array($result))
        {
            $permissionCurr = new permission($currRow['id'], $currRow['data_creazione'],$currRow['profilo'],$currRow['fk_packet'],$currRow['fk_utenteweb']);
            $lista[$ind] = $permissionCurr;
            $ind++;
        }
        
        if ($ind==0){
        	$lista[0] = new permission(0,"","","","");
        }
        
        return $lista;
	}
	
	public function getPacketByUsername($username)
	{
		$querySelectUser = "SELECT * FROM packet WHERE owner='$username'";
        $result = $this->conn->executeQuery($querySelectUser);
        
        $currRow = mysql_fetch_array($result);
        
        $packetCurr = new packet($currRow['id'], $currRow['owner'], $currRow['data_creazione'],$currRow['stato'],$currRow['descrizione']);

        return $packetCurr;
	}
	
	public function getPacketById($idPacket)
	{
		$querySelectUser = "SELECT * FROM packet WHERE id='$idPacket'";
        $result = $this->conn->executeQuery($querySelectUser);
        
        $currRow = mysql_fetch_array($result);
        
        $packetCurr = new packet($currRow['id'], $currRow['owner'], $currRow['data_creazione'],$currRow['stato'],$currRow['descrizione']);

        return $packetCurr;
	}
	
	
	public function insertPermissionUserWeb($username,$profilo,$packet)
	{
		if($profilo[0]==PROFILO_AMMINISTRATORE){
	    	$queryInsert  = "INSERT INTO permission (data_creazione, fk_utenteweb, profilo,fk_packet) values(now(),'$username','$profilo[0]',$packet )";
			$this->conn->executeUpdate($queryInsert);
		}
		if($profilo[1]==PROFILO_UTENTE){
			$queryInsert  = "INSERT INTO permission (data_creazione, fk_utenteweb, profilo,fk_packet) values(now(),'$username','$profilo[1]',$packet )";
			$this->conn->executeUpdate($queryInsert);
		}

		return true;
	}
	
	
	public function insertPacketWeb($owner,$descrizione,$stato)
	{
		$queryInsert  = "INSERT INTO packet (data_creazione, owner,stato,descrizione) values(now(),'$owner','$stato','$descrizione')";
		$this->conn->executeUpdate($queryInsert);
		$idPacketCreato = mysql_insert_id();
		return $idPacketCreato;
	}
	
	public function modificaPacketWeb($idPacket,$owner,$descrizione,$stato)
	{
		$queryUpdate  = "UPDATE packet SET owner='$owner', stato='$stato', descrizione='$descrizione' WHERE id='$idPacket' ";
		$this->conn->executeUpdate($queryUpdate);
		
		return true;
	}
	
	/**
	 * Cambia il riferimento al Packet per l'utente selezionato.Operazione fattibile solo da super amministratore.
	 * Enter description here ...
	 * @param unknown_type $idPacket
	 * @param unknown_type $matricolaUtente
	 */
	public function modificaPacketPerUtenteWeb($idPacket,$matricolaUtente)
	{
		$queryUpdate  = "UPDATE permission SET fk_packet='$idPacket' WHERE fk_utenteweb='$matricolaUtente' ";
		$this->conn->executeUpdate($queryUpdate);
		
		return true;
	}
	
	public function modificaUtenteWeb($username,$stato,$profilo)
	{
		$queryUpdate = "UPDATE utente SET stato='$stato', profilo='$profilo' WHERE username='$username' ";
	
		$this->conn->executeUpdate($queryUpdate);
		
		return true;
	}
	
	public function listaPacchetti($stato)
	{
		$querySelect = "SELECT * FROM packet";
        if($stato <> '')
        {
            $querySelect = $querySelect." WHERE stato='$stato'";
        }

        $result = $this->conn->executeQuery($querySelect);
        $ind =0;
        $lista  = array();
        while ($currRow = mysql_fetch_array($result))
        {
            $packetCurr = new packet($currRow['id'], $currRow['owner'],$currRow['data_creazione'],$currRow['stato'],$currRow['descrizione']);
            $packetCurr->setAzione();
            $lista[$ind] = $packetCurr;
            $ind++;
        }
        
        return $lista;
	}
	
	/** Il metodo non recupera gli utetenti Super Amministratori */
	public function listUtentiWeb($stato)
	{
		$querySelectUsers = "SELECT * from utente ut";
        if($stato <> '')
        {
            $querySelectUsers = $querySelectUsers." AND stato='$stato'";
        }

        $result = $this->conn->executeQuery($querySelectUsers);
        $ind =0;
        $lista  = array();
        while ($currRow = mysql_fetch_array($result))
        {
        	$utenteCorrente = $currRow['username'];

        	
        	//Per ogni utente verifico i permessi posseduti
        	
            $utenteCurr = new utenteweb($currRow['username'], $currRow['stato'], $currRow['profilo']);
            
            $utenteCurr->setAzione();
            
            $lista[$ind] = $utenteCurr;
            $ind++;
        }
        
        return $lista;
	}
	
	public function deleteUtenteWeb($username)
	{
		$queryDelete  = "DELETE FROM utente WHERE upper(username)=upper('$username')";
		try {
			$this->conn->executeUpdate($queryDelete);
		}catch (Exception $e){
			throw new Exception(PREFIX_ERR.ERR_USER_DELETE);
		}
		return true;
	}
	
	public function deletePacket($idPacket)
	{
		$queryDelete  = "DELETE FROM packet WHERE id=$idPacket";
		try {
			$this->conn->executeUpdate($queryDelete);
		}catch (Exception $e){
			throw new Exception(PREFIX_ERR.ERR_PACKET_DELETE);
		}
		return true;
	}
	
	public function listUtentiByProfilo($profilo)
	{
		$querySelectUsers = "SELECT * FROM utente_web,permission WHERE fk_utenteweb = username and profilo='$profilo'";

        $result = $this->conn->executeQuery($querySelectUsers);
        $ind=0;
        $lista  = array();
        try{
	        while ($currRow = mysql_fetch_array($result))
	        {
	            $lista[$ind] = $currRow['username'];
	            $ind++;
	        }
	        if($ind==0)
	        	throw new Exception(ERR_NO_USER_FOUND);
	        else
	        	return $lista;
	        	
        }catch (Exception $e){
        	throw new Exception(ERR_NO_USER_FOUND);
        }
	}
	
	public function insertOperatore($nome,$cognome,$email,$indirizzo,$citta,$cap,$provincia,$nazione,$cellulare,$telefono,$fax,$packetID)
	{
		try {
		    $queryInsert  = "INSERT INTO operatore (nome,cognome,indirizzo,cap,citta,provincia,nazione,email,cellulare,telefono,fax,fk_packet) 
		    values('$nome','$cognome','$indirizzo','$cap','$citta','$provincia','$nazione','$email','$cellulare','$telefono','$fax','$packetID')";
			$this->conn->executeUpdate($queryInsert);
			
		} catch (Exception $e) {
			throw new Exception(ERR_INSERT_OPERATOR);
		}		    
		return true;
	}
	
	public function listOperatori($pacchettoID)
	{
		$querySelect = "SELECT * FROM operatore WHERE fk_packet='$pacchettoID' ";

        $result = $this->conn->executeQuery($querySelect);
        $ind =0;
        $lista  = array();
        while ($currRow = mysql_fetch_array($result))
        {      	
            $opCurr = new operatore($currRow['id'],$currRow['nome'],$currRow['cognome'],$currRow['indirizzo'],$currRow['cap'],$currRow['citta'],$currRow['provincia'],$currRow['nazione'],$currRow['email'],$currRow['cellulare'],$currRow['telefono'],$currRow['fax'],$currRow['fk_packet']);
            
            $lista[$ind] = $opCurr;
            $ind++;
        }
        
        return $lista;
	}
	

	

	
	public function resetPwdUtente($username)
	{
		try{
		    $MD5pwd = md5($username);
	
	        $queryUpdate = "UPDATE utente_web SET pwd='$MD5pwd' WHERE username='$username'";
	        $result = $this->conn->executeQuery($queryUpdate);
	        
		}catch (Exception $e) {
			throw new Exception(ERR_RESET_PWD_USER);
		}		    
		return true;	
	}
	
	public function deleteAllPermissionUser($username,$packetID)
	{
		$queryDelete  = "DELETE FROM permission WHERE fk_utenteweb='$username' AND fk_packet=$packetID";
		try {
			$this->conn->executeUpdate($queryDelete);
		}catch (Exception $e){
			//Non ha cancellato i permessi perche non esistono. Ignorato.
		}
		return true;
	}
	
	public function listUtentiSuperAdmin($stato)
	{
		$querySelectUsers = "SELECT ut.* FROM utente_web ut, permission p WHERE ut.username = p.fk_utenteweb AND p.profilo=".PROFILO_SUPERADMIN;
        if($stato <> '')
        {
            $querySelectUsers = $querySelectUsers." AND stato='$stato'";
        }

        $result = $this->conn->executeQuery($querySelectUsers);
        $ind =0;
        $lista  = array();
        while ($currRow = mysql_fetch_array($result))
        {
            $utenteCurr = new utenteweb($currRow['username'], $currRow['stato'], $currRow['email'],$currRow['fk_operatore']);
            //Non mi serve nella tabella
            $utenteCurr->setIdProfili(0);
            
            $utenteCurr->setAzione();
            
            $lista[$ind] = $utenteCurr;
            $ind++;
        }
        
        return $lista;
	}
	
	public function aggiornaLegameOperatoreUtente($username,$idOperatore)
	{
		try{
	        $queryUpdate = "UPDATE utente_web SET fk_operatore='$idOperatore' WHERE username='$username'";
	        $result = $this->conn->executeQuery($queryUpdate);
	        
		}catch (Exception $e) {
			throw new Exception(ERR_AGGIORNAMENTO_OPERATORE_UTENTE);
		}		    
		return true;	
	}
	
	public function deleteUtenteWebForOperatorID($idOperatore)
	{
		$queryDelete  = "DELETE FROM utente_web WHERE fk_operatore='$idOperatore'";
		try {
			$this->conn->executeUpdate($queryDelete);
		}catch (Exception $e){
			throw new Exception(PREFIX_ERR.ERR_DELETE_UTENTE_WEB_FOR_OPERATOR);
		}
		return true;
	}
	
	/**
	 * Restituisce tutte le info dell'utente leggendo i dati dalle rispettive tabelle dei Profili.
	 * @param unknown_type $username
	 */
	public function getAllInfoUtente($username)
	{
		$querySelectUser = "SELECT ad.username,ad.stato,ad.profilo FROM utente ad WHERE upper(username)=upper('$username')";
		$result = $this->conn->executeQuery($querySelectUser);
	
		$currRow = mysql_fetch_array($result);
	
		$utenteCurr = new utenteweb($currRow['username'], $currRow['stato'], $currRow['profilo']);
		
		if($utenteCurr->profilo == PROFILO_AGENZIA ){
			
			$querySelectUserAgenzia = "SELECT * FROM agenzia WHERE upper(username)=upper('$username')";
			$resultAgenzia = $this->conn->executeQuery($querySelectUserAgenzia);
			
	        $currRowAgenzia = mysql_fetch_array($resultAgenzia);
	        
	        $utenteAgenzia = new utente_agenzia($currRowAgenzia['username'], $utenteCurr->stato, $utenteCurr->profilo, $currRowAgenzia['nome'], $currRowAgenzia['indirizzo'], $currRowAgenzia['cap'], $currRowAgenzia['citta'], $currRowAgenzia['provincia'], $currRowAgenzia['piva'], $currRowAgenzia['email'], $currRowAgenzia['telefono'], $currRowAgenzia['cellulare'], $currRowAgenzia['fax'], $currRowAgenzia['note'], $currRowAgenzia['codfiliale'], $currRowAgenzia['network'], $currRowAgenzia['intestazione'], $currRowAgenzia['licenza'], $currRowAgenzia['cameradicommercio']);
	        
	        return $utenteAgenzia;
		}
		else if($utenteCurr->profilo == PROFILO_FILIALE ){
			
			$querySelectUserFiliale = "SELECT * FROM filiale WHERE upper(username)=upper('$username')";
			$resultFiliale = $this->conn->executeQuery($querySelectUserFiliale);
			
	        $currRowFiliale = mysql_fetch_array($resultFiliale);
	        
	        $utenteFiliale = new utente_filiale($currRowFiliale['username'], $utenteCurr->stato, $utenteCurr->profilo, $currRowFiliale['nome'], $currRowFiliale['indirizzo'], $currRowFiliale['cap'], $currRowFiliale['citta'], $currRowFiliale['provincia'], $currRowFiliale['piva'], $currRowFiliale['email'], $currRowFiliale['telefono'], $currRowFiliale['cellulare'], $currRowFiliale['fax'], $currRowFiliale['note'], $currRowFiliale['codice']);

	        return $utenteFiliale;
		}
	
		return $utenteCurr;
	}
	
	public function modificaUtentePerProfilo($username,$stato,$profilo,$nome,$indirizzo,$cap,$citta,$provincia,$piva,$email,$telefono,$cellulare,$fax,$note,$codfiliale,$network,$intestazione,$licenza,$cameradicommercio,$codice)
	{
		//Aggiorno le informazioni dell'utente web
		$queryUpdate = "UPDATE utente SET stato='$stato', profilo='$profilo' WHERE username='$username' ";
	
		$this->conn->executeUpdate($queryUpdate);
		
		//Aggiorno le informazione dell'utente per profilo
		if($profilo == PROFILO_AGENZIA){
			
			$queryUpdateAgenzia = "UPDATE agenzia SET nome='$nome', indirizzo='$indirizzo', cap='$cap', citta='$citta', provincia='$provincia', piva='$piva', email='$email', telefono='$telefono', cellulare='$cellulare', fax='$fax', note='$note', codfiliale='$codfiliale', network='$network', intestazione='$intestazione', licenza='$licenza', cameradicommercio='$cameradicommercio' WHERE username='$username' ";
			
			$this->conn->executeUpdate($queryUpdateAgenzia);
			
		}else if($profilo == PROFILO_FILIALE){

			$queryUpdateFiliale = "UPDATE filiale SET nome='$nome', indirizzo='$indirizzo', cap='$cap', citta='$citta', provincia='$provincia', piva='$piva', email='$email', telefono='$telefono', cellulare='$cellulare', fax='$fax', note='$note', codice='$codice' WHERE username='$username' ";
			
			$this->conn->executeUpdate($queryUpdateFiliale);
			
		}
		
		return true;
	}
}
?>
